﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BoilerplateWPF.Helpers
{
    /// <summary>
    /// 数据库 帮助类 跟MySql.Data的MySqlHelper有一个字母的大小写不同，注意区分
    /// </summary>
    public static class MysqlHelper
    {
        //连接字符串
        public static string connectionString = ConfigHelper.GetValue("defaultConn");

        /// <summary>
        /// 测试  是否连接成功
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>



        // 执行不返回结果集的SQL语句
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 执行SQL语句并返回影响行数
                    return command.ExecuteNonQuery();
                }
            }
        }

        // 执行一个查询，并返回结果集中第一行的第一列
        public static object ExecuteScalar(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 执行SQL查询并返回第一行第一列的值
                    return command.ExecuteScalar();
                }
            }
        }

        // 执行一个查询，并返回结果集
        public static DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 后加：添加参数 加个非空判断
                    if (parameters != null)
                        command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataAdapter和DataTable对象，并填充数据
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
            }
        }
        //// 打印表头（列名）
        //foreach (DataColumn column in dataTable.Columns)
        //{
        //    Console.Write(column.ColumnName + "\t");
        //}
        //Console.WriteLine(); // 换行

        //// 打印每一行数据
        //foreach (DataRow row in dataTable.Rows)
        //{
        //    foreach (var item in row.ItemArray)
        //    {
        //        MessageBox.Show(item.ToString() + "\t");
        //    }
        //    Console.WriteLine(); // 换行
        //}

        // 执行一个查询，并将结果集映射到一个对象列表
        public static List<T> ExecuteQuery<T>(string sql, Func<IDataRecord, T> selector, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataReader对象并读取数据，将每行数据映射到对象并添加到列表中
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();
                        while (reader.Read())
                        {
                            list.Add(selector(reader));
                        }
                        return list;
                    }
                }
            }
        }

        // 向数据库中插入数据
        public static int Insert(string tableName, Dictionary<string, object> data)
        {
            string[] columns = new string[data.Count];
            object[] values = new object[data.Count];

            int i = 0;
            foreach (KeyValuePair<string, object> item in data)
            {
                // 获取列名和值
                columns[i] = item.Key;
                values[i] = item.Value;
                i++;
            }

            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, string.Join(",", columns), "@" + string.Join(",@", columns));

            // 将Dictionary转换为MySqlParameter数组，并执行SQL语句
            return ExecuteNonQuery(sql, ToMySqlParameters(data));
        }

        // 更新数据库中的数据
        public static int Update(string tableName, Dictionary<string, object> data, string whereClause = "")
        {
            string[] setValues = new string[data.Count];
            int i = 0;
            foreach (KeyValuePair<string, object> item in data)
            {
                // 获取列名和值
                setValues[i] = string.Format("{0}=@{0}", item.Key);
                i++;
            }

            string sql = string.Format("UPDATE {0} SET {1}", tableName, string.Join(",", setValues));

            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }

            // 将Dictionary转换为MySqlParameter数组，并执行SQL语句
            return ExecuteNonQuery(sql, ToMySqlParameters(data));
        }

        // 删除数据库中的数据
        public static int Delete(string tableName, string whereClause = "")
        {
            string sql = string.Format("DELETE FROM {0}", tableName);

            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }

            // 执行SQL语句并返回影响

            return ExecuteNonQuery(sql);
        }
        // 将Dictionary转换为MySqlParameter数组
        private static MySqlParameter[] ToMySqlParameters(Dictionary<string, object> data)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            foreach (KeyValuePair<string, object> item in data)
            {
                parameters.Add(new MySqlParameter("@" + item.Key, item.Value));
            }

            return parameters.ToArray();
        }

        /// <summary>
        /// 后加：批量执行同构SQL（参数不同）
        /// </summary>
        /// <param name="sqlTemplate">参数化SQL模板（如UPDATE语句）</param>
        /// <param name="parametersList">每条SQL对应的参数数组集合</param>
        /// <param name="useTransaction">是否启用事务（默认true）</param>
        /// <returns>所有SQL语句影响的行数总和，失败时返回0并抛出异常</returns>
        public static int ExecuteBatch(
            string sqlTemplate,
            IEnumerable<MySqlParameter[]> parametersList,
            bool useTransaction = true)
        {
            int totalAffectedRows = 0; // 累计受影响行数
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlTransaction transaction = useTransaction ? conn.BeginTransaction() : null;
                try
                {
                    foreach (var parameters in parametersList)
                    {
                        using (MySqlCommand cmd = new MySqlCommand(sqlTemplate, conn, transaction))
                        {
                            if (parameters != null)
                            {
                                cmd.Parameters.AddRange(parameters);
                            }
                            totalAffectedRows += cmd.ExecuteNonQuery(); // 累加行数
                        }
                    }
                    if (useTransaction)
                        transaction.Commit();
                    return totalAffectedRows;
                }
                catch (Exception ex)
                {
                    if (useTransaction)
                    {
                        transaction.Rollback();
                        throw new Exception("事务已回滚，所有修改未生效", ex); // 明确提示行数无效
                    }
                    else
                    {
                        throw new Exception($"部分执行成功，已影响行数：{totalAffectedRows}", ex);
                    }
                }
            }
        }

        /// <summary>
        /// 后加：查询 无需参数 返回List
        /// </summary>
        public static List<T> QueryList<T>(string sql, Func<IDataRecord, T> selector)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 打开连接（无参数添加步骤）
                    connection.Open();
                    // 创建DataReader对象并读取数据
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();
                        while (reader.Read())
                        {
                            list.Add(selector(reader));
                        }
                        return list;
                    }
                }
            }
        }

    }
}
